package cn.funnymap.lgis.file.excel;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.nio.file.Path;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @author jiaoxn
 */
public class ExcelOperator {
    private ExcelOperator() {}

    public static List<ExcelTreeItem> readAsTree(Path excelFilePath,
                                                 Integer sheetIndex,
                                                 Boolean firstRowIsTitle) throws IOException {
        Workbook workbook = generateWorkbook(excelFilePath);
        return readContentAsTree(workbook, sheetIndex, firstRowIsTitle);
    }

    public static List<ExcelTreeItem> readAsTree(MultipartFile multipartFile,
                                                 Integer sheetIndex,
                                                 Boolean firstRowIsTitle) throws IOException {
        Workbook workbook = generateWorkbook(multipartFile);
        return readContentAsTree(workbook, sheetIndex, firstRowIsTitle);
    }

    private static Workbook generateWorkbook(MultipartFile multipartFile) throws IOException {
        return WorkbookFactory.create(multipartFile.getInputStream());
    }

    private static Workbook generateWorkbook(Path excelFilePath) throws IOException {
        return WorkbookFactory.create(excelFilePath.toFile());
    }

    private static List<ExcelTreeItem> readContentAsTree(Workbook workbook,
                                                         Integer sheetIndex,
                                                         Boolean firstRowIsTitle) {
        Sheet sheet = workbook.getSheetAt(sheetIndex);

        List<ExcelTreeItem> excelTreeItemList = new ArrayList<>();

        if (Boolean.TRUE.equals(firstRowIsTitle)) {
            sheet.removeRow(sheet.getRow(0));
        }

        for (Row row : sheet) {
            ExcelTreeItem tempItem = null;

            for (Cell cell : row) {
                String cellValue = readCellValueAsString(sheet, cell);

                if (!StringUtils.hasLength(cellValue)) {
                    continue;
                }

                if (cell.getColumnIndex() == 0) {
                    // 每行的第一列，优先从数组中查询，如果没有，则创建一个新的实体
                    tempItem = excelTreeItemList
                            .stream()
                            .filter(item -> Objects.equals(item.getValue(), cellValue))
                            .findAny()
                            .orElseGet(() -> {
                                excelTreeItemList.add(new ExcelTreeItem(cellValue));
                                return excelTreeItemList.get(excelTreeItemList.size() - 1);
                            });
                } else {
                    assert tempItem != null;

                    // 其余列，优先从第一列的children中查询
                    ExcelTreeItem excelTreeItem = tempItem.getChildren()
                            .stream()
                            .filter(item -> Objects.equals(item.getValue(), cellValue))
                            .findAny()
                            .orElse(null);

                    // 如果没有，则创建一个新的实体
                    if (excelTreeItem == null) {
                        excelTreeItem = new ExcelTreeItem(cellValue);
                        tempItem.getChildren().add(excelTreeItem);
                    }

                    tempItem = excelTreeItem;
                }
            }
        }

        return excelTreeItemList;
    }

    private static String readCellValueAsString(Sheet sheet, Cell cell) {
        String cellValue = cell.getStringCellValue();

        CellAddress cellAddress = cell.getAddress();
        if (isMergedCell(sheet, cellAddress)) {
            CellAddress mergedRegionCellAddress = getMergedRegionCellAddress(sheet, cellAddress);

            if (mergedRegionCellAddress != null) {
                cellValue = readCellValueAsString(
                        sheet.getRow(mergedRegionCellAddress.getRow()).getCell(mergedRegionCellAddress.getColumn()));
            }
        }

        return cellValue;
    }

    private static String readCellValueAsString(Cell cell) {
        if(cell == null) {
            return "";
        }

        if(cell.getCellType() == CellType.STRING){
            return cell.getStringCellValue();
        } else if(cell.getCellType() == CellType.BOOLEAN){
            return String.valueOf(cell.getBooleanCellValue());
        } else if(cell.getCellType() == CellType.FORMULA){
            return cell.getCellFormula() ;
        } else if(cell.getCellType() == CellType.NUMERIC){
            return String.valueOf(cell.getNumericCellValue());
        }

        return "";
    }

    private static boolean isMergedCell(Sheet sheet, CellAddress cellAddress) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            if (sheet.getMergedRegion(i).isInRange(cellAddress)) {
                return true;
            }
        }

        return false;
    }

    private static CellAddress getMergedRegionCellAddress(Sheet sheet, CellAddress cellAddress) {
        for (int i = 0; i < sheet.getNumMergedRegions(); i++) {
            CellRangeAddress region = sheet.getMergedRegion(i);
            if (region.isInRange(cellAddress)) {
                return new CellAddress(region.getFirstRow(), region.getFirstColumn());
            }
        }
        return null;
    }
}
